﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

namespace syc.Fc.报表
{
    public partial class 五分厂油管管体月生产报表2 : System.Web.UI.Page
    {
        protected JinDuMonthData jindu = new JinDuMonthData();
        protected HSSFWorkbook hssfworkbook;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.NianDu.Text = DateTime.Today.Year.ToString();
                this.YueFen.Text = DateTime.Today.Month.ToString();
            }
            this.QueryData();
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("模板/五分厂油管管体月生产报表2.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "五分厂油管管体月生产报表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void WriteToFile()
        {
            DateTime d;
            if (!DateTime.TryParse(this.RiQi.Text, out d))
            {
                d = DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("油管月生产报表[" + d.ToLongDateString()) + "].xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }


        private void QueryData()
        {
            DateTime d1, d2, d3;

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;

            Cmd.CommandText =
@"declare @d1 smalldatetime
declare @d2 smalldatetime
if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=@m)
begin
	select @d1=起始日期,@d2=截止日期 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=@m
	if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1)
	begin
		select @d1 as 月初,@d2 as 月末,起始日期 as 年初 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1
	end
end";
            Cmd.CommandText = Cmd.CommandText.Replace("@y", this.NianDu.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@m", this.YueFen.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "5");
            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d1 = (DateTime)Reader["月初"];
                d2 = (DateTime)Reader["月末"];
                d3 = (DateTime)Reader["年初"];
                this.RiQi.Text = d1.ToLongDateString();
                this.RiQi2.Text = d2.ToLongDateString();
                this.RiQi3.Text = d3.ToLongDateString();
            }
            else
            {
                this.RiQi.Text = this.NianDu.Text + "年" + this.YueFen.Text + "月" + "1日";
                d1 = DateTime.Parse(this.RiQi.Text);
                d2 = d1.AddMonths(1).AddDays(-1);
                this.RiQi2.Text = d2.ToLongDateString();
                this.RiQi3.Text = this.NianDu.Text + "年1月1日";
                d3 = DateTime.Parse(this.RiQi3.Text);
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
            jindu.QueryData(5, 2, 1, d1, d2, d3);
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            this.QueryData();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("数据");
            int r = 0;
            HSSFRow row;
            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("日期");
            row.CreateCell(1).SetCellValue(this.RiQi2.Text);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("生产单位");
            row.CreateCell(1).SetCellValue("五分厂2号线");

            string op = (string)this.Session["OperatorName"];
            if (op == null)
            {
                op = "";
            }
            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("批准");
            row.CreateCell(1).SetCellValue("");

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("制表人");
            row.CreateCell(1).SetCellValue(op);

            string[] field = { 
                             
"计划支数",
"计划重量",
"投入支数",
"投入重量",
"机紧1",
"机紧2",
"静水压",
"称测支数",
"称测长度",
"称测重量",
"包装支数",
"包装捆数",
"包装长度",
"包装重量",
"入库支数",
"入库捆数",
"入库长度",
"入库重量",
"未入库支数",
"未入库重量",
"车丝1",
"车丝2",
"车丝3",
"车丝4",
"车丝5",
"车丝6",
"车丝7",
"车丝8",
"车丝9",
"车丝10"};

            string banci = "";
            string leiji = "";
            for (int i = 0; i < 8; i++)
            {
                if (i == 0 || i == 4)
                {
                    banci = "甲班";
                }
                else if (i == 1 || i == 5)
                {
                    banci = "乙班";
                }
                else if (i == 2 || i == 6)
                {
                    banci = "丙班";
                }
                else if (i == 3 || i == 7)
                {
                    banci = "丁班";
                }
                else
                {
                    banci = "";
                }

                if (i < 4)
                {
                    leiji = "";
                }

                else
                {
                    leiji = "累计";
                }
                for (int j = 0; j < 30; j++)
                {
                    row = sheet1.CreateRow(r++);
                    row.CreateCell(0).SetCellValue(banci + field[j] + leiji);
                    row.CreateCell(1).SetCellValue(jindu.Data[i * 30 + j]);
                }
            }

            HSSFSheet sheet2 = hssfworkbook.GetSheet("报表");
            sheet2.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

    }
}
